﻿using DHQGHN.DAL.Base;
using DHQGHN.DAL.Models.DHQG;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DHQGHN.DAL.DAO.DHQG
{
    public class ExportExcellDAO:SqlDAO
    {
        public const string PROCEDURE_KE_HOACH_THUC_HIEN = "spKehoach_ThucHien_GetByNamKeHoach_NhomDonViID_SUM";
        public const string PROCEDURE_KE_HOACH = "spKehoach_GetByNamKeHoach_StatusId_SUM";//dhqg

        public const string PROCEDURE_CHI_TIEU_DINH_HUONG_DHQG = "spKeHoach_ChiTieu_Sum";
        public const string PROCEDURE_CHI_TIEU_DHQG = "[spKeHoach_ChiTieu_DHQG]";
        public const string PROCEDURE_CHI_TIEU_DON_VI = "spKeHoach_ChiTieu_DonVi";
        public const string PROCEDURE_KH_THUC_HIEN = "spKeHoachThucHien_ChiTieu_Sum";
        public const string PROCEDURE_KH_THUC_HIEN_DON_VI="spKeHoachThucHien_ChiTieu_DonVi";
        public const string PROCEDURE_KH_THUC_HIEN_DON_VI2 = "spKeHoachThucHien_ChiTieu_DonVi2";
        public const string PROCEDURE_KH_THUC_HIEN_BAN_PHU_TRACH = "spKeHoach_Ban_Phu_Trach";
        public const string PROCEDURE_KE_HOACH_BAN_PHU_TRACH = "spKeHoach_Ban_Phu_Trach_TheoDonVi";

        public const string PROCEDURE_KH_THUC_HIEN_DON_VI_THEO_NHOM ="spKeHoachThucHien_ChiTieu_DonVi_TheoNhomDonVi";
        public const string PROCEDURE_KH_THUC_HIEN_DON_VI_THEO_NHOM2 = "spKeHoachThucHien_ChiTieu_DonVi_TheoNhomDonVi2";
        public const string PROCEDURE_KH_THUC_HIEN_DHQG_THEO_NHOM = "spKeHoachThucHien_ChiTieu_Sum_TheoNhomDonVi_DHQG";
        public const string PROCEDURE_KE_HOACH_DHQG_THEO_NHOM = "spKeHoach_ChiTieu_TheoNhomDonVi_DHQG";
        public const string PROCEDURE_KE_HOACH_DON_VI_THEO_NHOM = "spKeHoach_ChiTieu_DonVi_TheoNhomDonVi";
        public const string PROCEDURE_KE_HOACH_THUC_HIEN_DON_VI_CHI_TIEU_RIENG = "spKeHoachThucHien_ChiTieuRieng_DonVi";
        public const string PROCEDURE_KE_HOACH_DON_VI_CHI_TIEU_RIENG = "spKeHoach_ChiTieuRieng_DonVi";
        public const string PROCEDURE_KE_HOACH_GET_BY_CHI_TIEU_ID = "spKeHoach_GetByChiTieuId";
        public const string PROCEDURE_COMMENT_GET_BY_CHI_TIEU_ID = "spComment_GetByChiTieuId";
        //spKehoach_ChinhThuc_ByNamKeHoach lay du lieu cac don vi
        public const string PARAM_NAM_KE_HOACH_ID = "@NAM_KE_HOACH_ID";
        public const string PARAM_STATUS_ID = "@STATUS_ID";
        public const string PARAM_NHOM_DON_VI_ID = "@NHOM_DON_VI_ID";
        public const string PARAM_DON_VI_ID = "@DON_VI_ID";
        public const string PARAM_MA_CAP_1 = "@MA_CAP1";
        public const string PARAM_CHI_TIEU_ID = "@CHI_TIEU_ID";
        public const string PARAM_MOC_KIEM_SOAT_ID = "@MOC_KIEM_SOAT_ID";
    
        public ExportExcellDAO(string connectionString, System.Type type): 
            base(connectionString, type){ }

        public List<ExportExcell> GetDataKeHoachDHQG(int namKeHoachId, int statusId, string maCap1, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_KE_HOACH);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKeHoachId));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));
                cmd.Parameters.Add(CreateParameter(PARAM_MA_CAP_1, maCap1));

                return GetList<ExportExcell>(cmd, mapDictionary);

            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetDataKeHoachDonVi(int namKeHoachId, int donViId, int nhomDonViId, int statusId, string maCap1, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spKehoach_ChinhThuc_ByNamKeHoach");
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKeHoachId));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));
                cmd.Parameters.Add(CreateParameter(PARAM_MA_CAP_1, maCap1));
                cmd.Parameters.Add(CreateParameter(PARAM_NHOM_DON_VI_ID, nhomDonViId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));

                return GetList<ExportExcell>(cmd, mapDictionary);

            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetDataKeHoachThucHienDHQG(int namKeHoachId, string maCap1, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_KE_HOACH_THUC_HIEN);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKeHoachId));
                cmd.Parameters.Add(CreateParameter(PARAM_MA_CAP_1, maCap1));               

                return GetList<ExportExcell>(cmd,  mapDictionary);

            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetDataKeHoachThucHienDonVi(int namKeHoachId, int donViId, int nhomDonViId, string maCap1, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spKehoach_ThucHien_GetByNamKeHoach_BaoCao");
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKeHoachId));
                cmd.Parameters.Add(CreateParameter(PARAM_NHOM_DON_VI_ID, nhomDonViId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));
                cmd.Parameters.Add(CreateParameter(PARAM_MA_CAP_1, maCap1));

                return GetList<ExportExcell>(cmd, mapDictionary);

            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetListChiTieuDHQG(int namKH, int statusId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_CHI_TIEU_DHQG);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKH));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));                

                return GetList<ExportExcell>(cmd, mapDictionary);

            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetListChiTieuDonVi(int namKH, int statusId, int donViId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_CHI_TIEU_DON_VI);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKH));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));

                return GetList<ExportExcell>(cmd, mapDictionary);
            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetListKeHoachThucHienDHQG(int namKH, int statusId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_KH_THUC_HIEN);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKH));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));

                return GetList<ExportExcell>(cmd, mapDictionary);

            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetListKeHoachThucHienDonVi(int namKH, int statusId, int donViId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_KH_THUC_HIEN_DON_VI);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKH));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));

                return GetList<ExportExcell>(cmd, mapDictionary);

            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetListKeHoachThucHienDonVi2(int namKH, int statusId, int donViId, int mocKS, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_KH_THUC_HIEN_DON_VI2);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKH));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));
                cmd.Parameters.Add(CreateParameter(PARAM_MOC_KIEM_SOAT_ID, mocKS));

                return GetList<ExportExcell>(cmd, mapDictionary);

            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetListBanPhuTrach(int namKH, int statusId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_KH_THUC_HIEN_BAN_PHU_TRACH);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKH));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));

                return GetList<ExportExcell>(cmd, mapDictionary);
            }
            catch
            {
                
                throw;
            }
        }

        public List<ExportExcell> GetListChiTieuDHQG_TheoNhom(int namKH, int statusId, int donViId, int nhomDonViId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_KE_HOACH_DHQG_THEO_NHOM);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKH));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));
                cmd.Parameters.Add(CreateParameter(PARAM_NHOM_DON_VI_ID, nhomDonViId));

                return GetList<ExportExcell>(cmd, mapDictionary);

            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetListChiTieuDonVi_TheoNhom(int namKH, int statusId, int donViId, int nhomDonViId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_KE_HOACH_DON_VI_THEO_NHOM);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKH));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));
                cmd.Parameters.Add(CreateParameter(PARAM_NHOM_DON_VI_ID, nhomDonViId));

                return GetList<ExportExcell>(cmd, mapDictionary);
            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetListKeHoachThucHienDHQG_TheoNhom(int namKH, int statusId, int donViId, int nhomDonViId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_KH_THUC_HIEN_DHQG_THEO_NHOM);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKH));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));
                cmd.Parameters.Add(CreateParameter(PARAM_NHOM_DON_VI_ID, nhomDonViId));

                return GetList<ExportExcell>(cmd, mapDictionary);

            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetListKeHoachThucHienDonVi_TheoNhom(int namKH, int statusId, int donViId, int nhomDonViId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_KH_THUC_HIEN_DON_VI_THEO_NHOM);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKH));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));
                cmd.Parameters.Add(CreateParameter(PARAM_NHOM_DON_VI_ID, nhomDonViId));

                return GetList<ExportExcell>(cmd, mapDictionary);

            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetListKeHoachThucHienDonVi_TheoNhom2(int namKH, int statusId, int donViId, int mocKS, int nhomDonViId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_KH_THUC_HIEN_DON_VI_THEO_NHOM2);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKH));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));
                cmd.Parameters.Add(CreateParameter(PARAM_NHOM_DON_VI_ID, nhomDonViId));
                cmd.Parameters.Add(CreateParameter(PARAM_MOC_KIEM_SOAT_ID, mocKS));

                return GetList<ExportExcell>(cmd, mapDictionary);

            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetListChiTieuRiengDonVi_KeHoachThucHien(int namKhId, int donViID, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_KE_HOACH_THUC_HIEN_DON_VI_CHI_TIEU_RIENG);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKhId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViID));

                return GetList<ExportExcell>(cmd, mapDictionary);
            }
            catch
            {                
                throw;
            }
        }

        public List<ExportExcell> GetListChiTieuRiengDonVi_KeHoach(int namKhId, int donViId, int statusId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_KE_HOACH_DON_VI_CHI_TIEU_RIENG);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKhId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));

                return GetList<ExportExcell>(cmd, mapDictionary);
            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetListBanPhuTrach_TheoDonVi(int namKH, int statusId, int donViId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_KE_HOACH_BAN_PHU_TRACH);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKH));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));

                return GetList<ExportExcell>(cmd, mapDictionary);
            }
            catch
            {

                throw;
            }
        }

        public int GetSoLuongDonViSuDungChiTieu(int namKhId, int chiTieuId)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spDonViChiTieu_CountDonVi");
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKhId));
                cmd.Parameters.Add(CreateParameter("@CHI_TIEU_ID", chiTieuId));

                object resultScalar = ExecuteScalar(cmd);
                int result = 0;
                if (resultScalar != DBNull.Value)
                {
                    result = Convert.ToInt32(resultScalar);
                }

                return result;
            }
            catch
            {
                throw;
            }
        }

        public bool DaDieuChinh(int namKhId, int statusId)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spKeHoach_DanhSachDieuChinh");
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKhId));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));

                object resultScalar = ExecuteScalar(cmd);
                int result = 0;
                if (resultScalar != DBNull.Value)
                {
                    result = Convert.ToInt32(resultScalar);
                }

                return result > 0 ? true : false;
            }
            catch 
            {
                throw;
            }
        }

        public List<ExportExcell> GetChiTieuDinhHuong(int namKhId, int statusId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_CHI_TIEU_DINH_HUONG_DHQG);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKhId));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));

                return GetList<ExportExcell>(cmd, mapDictionary);

            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetListChiTieuDonViTrongBangComment(int namKhId, int donViId, int statusId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spComment_ChiTieu_DonVi");
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKhId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));

                return GetList<ExportExcell>(cmd, mapDictionary);
            }
            catch
            {
                throw;
            }
        }


        public List<ExportExcell> GetListChiTieuTrongBangComment(int namKhId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spComment_ChiTieu");
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKhId));

                return GetList<ExportExcell>(cmd, mapDictionary);
            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetListChiTieuDinhHuongTrongBangComment(int namKhId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spComment_ChiTieu_DinhHuong");
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKhId));

                return GetList<ExportExcell>(cmd, mapDictionary);
            }
            catch
            {
                throw;
            }
        }

        public ExportExcell GetByChiTieuIdInCommentTable(int namKhId, int donViId, int statusId, int chiTieuId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_COMMENT_GET_BY_CHI_TIEU_ID);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKhId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));
                cmd.Parameters.Add(CreateParameter(PARAM_CHI_TIEU_ID, namKhId));

                return GetSingle<ExportExcell>(cmd, mapDictionary);

            }
            catch
            {
                throw;
            }
        }

        public ExportExcell GetByChiTieuIdInKeHoachTable(int namKhId, int donViId, int statusId, int chiTieuId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_KE_HOACH_GET_BY_CHI_TIEU_ID);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKhId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));
                cmd.Parameters.Add(CreateParameter(PARAM_CHI_TIEU_ID, namKhId));

                return GetSingle<ExportExcell>(cmd, mapDictionary);

            }
            catch
            {
                throw;
            }
        }

        public List<ExportExcell> GetListChiTieuCacDonViKhongDeXuat(int namKhId, int donViId, int statusId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spComment_ChiTieu_DonVi_KhongDeXuat");
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namKhId));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donViId));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, statusId));

                return GetList<ExportExcell>(cmd, mapDictionary);
            }
            catch
            {
                throw;
            }
        }
    } 

}
